﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Ledict.Core.Data;
using System.Data.Common;
using Ledict.ConditionExtract.Model;

namespace Ledict.ConditionExtract.DAL
{
   public class BatchEditCaseCauseDAL
    {
       private  string DbName;
       //private  string CommonDbName;
       public BatchEditCaseCauseDAL()
       {
          DbName = "JudgeDoc";
          //CommonDbName = "LdtCommonManage";
       }
       /// <summary>
       /// 获得数据列表(所有下级)
       /// </summary>
       public DataTable GetNeedDate( )
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(@"select * from dbo.SYS_CaseCause as a 
           where CC_NodeType=2    and ','+AllParentId+','  like '%,1000,%'
           and ( select count(*) from  dbo.SYS_CaseCause  
           where CC_ParentID=a.CC_ID  )>0
           and ( select count(*) from  dbo.SYS_CaseCause  
           where ','+AllParentId+',' like '%,'+a.CC_ID+',%' and CC_NodeType=2 and CC_ID<>a.CC_ID )=0
           and  ','+AllParentId+',' not like '%,1111111,%'
           and  ','+AllParentId+',' not like '%,2222222,%'
           and  ','+AllParentId+',' not like '%,27565,%'
           and  ','+AllParentId+',' not like '%,1410,%'
           and  ','+AllParentId+',' not like '%,3333333,%'
           and  ','+AllParentId+',' not like '%,CC1509101516430001,%'
           and  ','+AllParentId+',' not like '%,CC1509141224060001,%'
           and  ','+AllParentId+',' not like '%,1181,%'
           and  ','+AllParentId+',' not like '%,1145,%'
           and  ','+AllParentId+',' not like '%,1290,%'
           
        
           ");
           Database oBase = DatabaseFactory.CreateDatabase(DbName);
           DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
    
           DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
           if (OtB.Rows.Count > 0)
           {
               return OtB;
           }
           else
           {
               return new DataTable();
           }
       }

    

       public bool UpdateParent_FD(string  oldPid,string newPid )
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(@" update dbo.SYS_CaseCause set CC_ParentID=@newPid
           where CC_ID in (select CC_ID from dbo.SYS_CaseCause
           where (CC_Name='如实供述自己的罪行（坦白）'
           or CC_Name='有立功表现'
           or CC_Name='残疾人犯罪'
           or CC_Name='共犯'
           or CC_Name like '已满十六周岁不满十八周岁的人%'
           or CC_Name like '已满十四周岁不满十六周岁的人%')
           and 
           CC_ParentID=@oldPid )
	");

           Database oBase = DatabaseFactory.CreateDatabase(DbName);
           DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
           oBase.AddInParameter(oCmd, "@oldPid", DbType.String, oldPid);
           oBase.AddInParameter(oCmd, "@newPid", DbType.String, newPid);
          
           if (oBase.ExecuteNonQuery(oCmd) > 0)
           {
               return true;
           }
           return false;
       }

       public bool UpdateParent_ZD(string oldPid, string newPid)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(@" update dbo.SYS_CaseCause set CC_ParentID=@newPid
           where CC_ID in (select CC_ID from dbo.SYS_CaseCause
           where (CC_Name='积极退赃、退赔'
           or CC_Name='没有参与分赃'
           or CC_Name='参与分赃且获赃较少'
           or CC_Name='赃物、赃款已追回'
           or CC_Name ='获被害人谅解'
           or CC_Name ='未给被害人造成实际损失'
           or CC_Name ='偶犯'
           or CC_Name ='有犯罪前科及劳教劣迹'
           or CC_Name ='有认罪、悔罪表现'
           or CC_Name ='犯罪集团的首要分子'
           )and CC_ParentID=@oldPid
           )
	");

           Database oBase = DatabaseFactory.CreateDatabase(DbName);
           DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
           oBase.AddInParameter(oCmd, "@oldPid", DbType.String, oldPid);
           oBase.AddInParameter(oCmd, "@newPid", DbType.String, newPid);

           if (oBase.ExecuteNonQuery(oCmd) > 0)
           {
               return true;
           }
           return false;
       }

       public bool UpdateParent_XF(string oldPid, string newPid)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(@" update dbo.SYS_CaseCause set CC_ParentID=@newPid
           where CC_ID in (
           select CC_ID from dbo.SYS_CaseCause
           where (CC_Name='判处管制'  or
           CC_Name='判处拘役'         or
           CC_Name='判处有期徒刑'     or
           CC_Name='判处无期徒刑'     or
           CC_Name='判处死刑'         or
           CC_Name='判处罚金'         or
           CC_Name='判处没收财产'     or
           CC_Name='判处剥夺政治权利' or
           CC_Name='判处驱逐出境'     or
           CC_Name='免予刑事处罚'     or
           CC_Name='判处缓刑'         or
           CC_Name='社区矫正'         or 
           CC_Name like '判处%有期徒刑'  
           )and CC_ParentID=@oldPid)
	");

           Database oBase = DatabaseFactory.CreateDatabase(DbName);
           DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
           oBase.AddInParameter(oCmd, "@oldPid", DbType.String, oldPid);
           oBase.AddInParameter(oCmd, "@newPid", DbType.String, newPid);

           if (oBase.ExecuteNonQuery(oCmd) > 0)
           {
               return true;
           }
           return false;
       }


       public bool UpdateParent_XFTime(string oldPid, string newPid)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(@" update dbo.SYS_CaseCause set CC_ParentID=@newPid
           where CC_ID in (
           select CC_ID from dbo.SYS_CaseCause
           where   CC_Name like '判处%有期徒刑' and   CC_Name <> '判处有期徒刑' 
           and CC_ParentID=@oldPid)
	");

           Database oBase = DatabaseFactory.CreateDatabase(DbName);
           DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
           oBase.AddInParameter(oCmd, "@oldPid", DbType.String, oldPid);
           oBase.AddInParameter(oCmd, "@newPid", DbType.String, newPid);

           if (oBase.ExecuteNonQuery(oCmd) > 0)
           {
               return true;
           }
           return false;
       }


       public bool UpdateParent_FZXG(string oldPid, string newPid)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(@" update dbo.SYS_CaseCause set CC_ParentID=@newPid
           where CC_ID in (
           select CC_ID from dbo.SYS_CaseCause
           where CC_ParentID=@oldPid and  CC_NodeType<>9 
           )
	");

           Database oBase = DatabaseFactory.CreateDatabase(DbName);
           DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
           oBase.AddInParameter(oCmd, "@oldPid", DbType.String, oldPid);
           oBase.AddInParameter(oCmd, "@newPid", DbType.String, newPid);

           if (oBase.ExecuteNonQuery(oCmd) > 0)
           {
               return true;
           }
           return false;
       }




    }
}
